package com.tuling.dao.impl;

import com.tuling.dao.UserDao;
import com.tuling.domain.User;
import com.tuling.util.DBUtils;
import com.tuling.util.DruidUtils;
import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryLoader;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

public class UserDaoImpl implements UserDao {
//简化对数据库的代码处理
    public QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
    @Override
    public int add(User user) {
        //1.获取数据的连接
       // Connection connection = DBUtils.getConnection();
//        Connection connection = DruidUtils.getConnection();
//        PreparedStatement pstmt=null;
//        try {
//            //2.获取执行sql语句的对象
//            String sql="INSERT INTO USER (email,phone,pwd,uname) VALUES(?,?,?,?)";
//            pstmt = connection.prepareStatement(sql);
//            //3.设置参数
//            pstmt.setString(1,user.getEmail());
//            pstmt.setString(2,user.getPhone());
//            pstmt.setString(3,user.getPwd());
//            pstmt.setString(4,user.getUname());
//
//            //4.执行
//            int row = pstmt.executeUpdate();
//            return  row;
//        } catch (SQLException e) {
//            e.printStackTrace();
//            //如果有异常关闭资源
//        }finally {
//          //  DBUtils.closeAll(connection,pstmt,null);
//            DruidUtils.closeAll(connection,pstmt,null);
//        }

        String sql="INSERT INTO USER (email,phone,pwd,uname) VALUES(?,?,?,?)";
        Object[] params={user.getEmail(),user.getPhone(),user.getPwd(),user.getUname()};
        try {
            queryRunner.update(sql,params);
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return 0;
    }

    @Override
    public User findByStr(String str) {
//        //1.获取数据的连接
//       // Connection connection = DBUtils.getConnection();
//        Connection connection = DruidUtils.getConnection();
//        PreparedStatement pstmt = null;
//        try {
//            //2.获取执行sql语句的对象
//            String sql = "SELECT id,email,phone,pwd,uname FROM USER whereemail = ? OR phone = ? ";
//            pstmt = connection.prepareStatement(sql);
//            //3.设置参数
//            pstmt.setString(1, str);
//            pstmt.setString(2, str);
//            //4.执行
//            ResultSet rs = pstmt.executeQuery();
//            User user = null;
//            if (rs.next()) {
//                int id = rs.getInt("id");
//                String email = rs.getString("email");
//                String phone = rs.getString("phone");
//                String pwd = rs.getString("pwd");
//                String uname = rs.getString("uname");
//                user = new User(id, email, phone, pwd, uname);
//
//            }
//            return user;
//        } catch (SQLException e) {
//            e.printStackTrace();
//            //如果有异常关闭资源
//        }finally {
//            //  DBUtils.closeAll(connection,pstmt,null);
//            DruidUtils.closeAll(connection,pstmt,null);

           String sql = "SELECT id,email,phone,pwd,uname FROM USER whereemail = ? OR phone = ? ";
           Object[] params={str,str};
        try {
            User query = queryRunner.query(sql, new BeanHandler<User>(User.class),params);
            return query;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
   }

    @Override
    public List<User> findAll() {
        String sql="SELECT * FROM USER";
        try {
            List<User> users = queryRunner.query(sql, new BeanListHandler<User>(User.class));
            return users;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public User findById(int id) {
        String sql ="select * from user where id =?";
        Object[] params={id};
        try {
            User query = queryRunner.query(sql, new BeanHandler<User>(User.class),params);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public int update(User user) {
        String sql="SELECT * FROM USER  email=?,phone=?,pwd=?,uname=? WHERE id=?";
        Object[] params = {user.getEmail(),user.getPhone(),user.getUname(),user.getId()};
        try {
            int query = queryRunner.update(sql,params);
            return query;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    @Override
    public int dal(int id) {
        String sql="delete from user where id=?";
        Object[] params = {id};
        try {
            int row = queryRunner.update(sql, params);
            return row;
        } catch (SQLException e) {
            e.printStackTrace();
        }


        return 0;
    }
}
